﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyChoVay.Database
{
    class DatabaseAccess
    {
        private SqlConnection sqlcon;
        private SqlDataAdapter sqladap;
        public DatabaseAccess()
        {
            sqlcon = null;
            sqladap = null;
        }
        //open 1 connection
        public SqlConnection OpenConnection()
        {
            string source = @"Data Source=DINHTHANG\SQLEXPRESS; Initial Catalog=QuanLyChoVay; Uid=sa; pwd=123456";
            sqlcon = new SqlConnection(source);
            if (sqlcon != null && sqlcon.State == ConnectionState.Open) sqlcon.Close();
            sqlcon.Open();

            return sqlcon;
        }
        //dong 1 connection
        public void CloseConnection(SqlConnection sqlcon)
        {
            if (sqlcon != null && sqlcon.State == ConnectionState.Open)
            {
                sqlcon.Close();
                sqlcon.Dispose();
            }
        }
        //voi storeprocedure select
        public DataSet ExecuteProcSelect(string procName, SqlParameter[] para)
        {
            //mo 1 ket noi
            sqlcon = this.OpenConnection();
            //tao sqldataadapter
            sqladap = new SqlDataAdapter(procName, sqlcon);
            sqladap.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (para != null)
            {
                sqladap.SelectCommand.Parameters.AddRange(para);
            }

            DataSet ds = new DataSet();
            sqladap.Fill(ds);
            //dong ket noi
            this.CloseConnection(sqlcon);
            return ds;

        }
        //voi storeprocedure insert,delete,update...
        public void ExecuteProc(string procName, SqlParameter[] para)
        {
            //mo 1 ket noi
            sqlcon = this.OpenConnection();
            //tao moi adapter
            SqlCommand cmd = new SqlCommand(procName, sqlcon);
            cmd.CommandType = CommandType.StoredProcedure;
            if (para != null)
            {
                cmd.Parameters.AddRange(para);
            }

            cmd.ExecuteNonQuery();
            //dong ket noi
            this.CloseConnection(sqlcon);
        }
    }
}
